/*
 * Created on Jan 24, 2009
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.querie.beans;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.StringReader;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.StringTokenizer;



/**
 * @author manish.mittal
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class QueryResults {

	String sTxtQuery = "";
	String sTxtActivity = "";
	String sTxtKeyString = "";
	String sTxtQueryStore = "";
	String sCQuery = "";
	ArrayList aKeys = null;
	ArrayList aKeyResults = null;
	ArrayList aTableNames = null;

	ArrayList aLogic = null;
	

	public QueryResults ()
	{
		aKeys = new ArrayList();
		aKeyResults = new ArrayList();
		aLogic = new ArrayList();
	}

	public String getSTxtKeyString() {
		return sTxtKeyString;
	}
	public void setSTxtKeyString(String txtKeyString) {
		sTxtKeyString = txtKeyString;
	}

	/**
	 * @return Returns the sTxtActivity.
	 */
	public String getSTxtActivity() {
		return sTxtActivity;
	}
	/**
	 * @param txtActivity The sTxtActivity to set.
	 */
	public void setSTxtActivity(String txtActivity) {
		sTxtActivity = txtActivity;
	}
	/**
	 * @return Returns the sTxtQuery.
	 */
	public String getSTxtQuery() {
		return sTxtQuery;
	}
	/**
	 * @param txtQuery The sTxtQuery to set.
	 */
	public void setSTxtQuery(String txtQuery) {
		sTxtQuery = txtQuery;
	}


public ArrayList getSkyServerResults() throws Exception
{
	int i = 1;
	int iNoOfCols = 0;

	ArrayList aList = new ArrayList();
	aList.add("" + iNoOfCols);

        System.out.println("Suju sTxtQuery: " + sTxtQuery);

	String data = URLEncoder.encode("format", "UTF-8") + "=" + URLEncoder.encode("csv", "UTF-8");
	data += "&" + URLEncoder.encode("cmd", "UTF-8") + "=" + URLEncoder.encode(sTxtQuery, "UTF-8");

	// Send data
	URL url = new URL("http://cas.sdss.org/dr7/en/tools/search/x_sql.asp");
	URLConnection conn = url.openConnection();
	conn.setDoOutput(true);
	OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream());
	wr.write(data);
	wr.flush();

	// Get the response
	BufferedReader rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
	String line;
	StringTokenizer st = null;
	int iIndex = 0;
	int jIndex = 0;
	String sDummy = "";
	int iNoOfKeys = 0;
	while ((line = rd.readLine()) != null) {
            


	iNoOfKeys = 0;
	iIndex = line.indexOf('"');
	jIndex = line.indexOf('"', iIndex+1);
	while(iIndex != -1)
	{
		sDummy = line.substring(iIndex, jIndex);
		sDummy = sDummy.replaceAll(",", ";");
		line = line.substring(0, iIndex) + sDummy + line.substring(jIndex);
		iIndex = line.indexOf('"', jIndex+1);

		jIndex = line.indexOf('"', iIndex+1);
	}

	iIndex = 0;
	jIndex = line.indexOf(',');
	while(jIndex != -1)
	{
		sDummy = line.substring(iIndex, jIndex);
		if(sDummy == null)
			sDummy = " ";
		iNoOfKeys = iNoOfKeys + 1;
		aList.add(sDummy);
		iIndex = jIndex + 1;
		jIndex = line.indexOf(',', iIndex);
		if(i == 1)
		iNoOfCols = iNoOfCols + 1;
	}
	if(i == 1)
		iNoOfCols = iNoOfCols + 1;
	aList.add(line.substring(iIndex));

	/*st = new StringTokenizer(line, ",");

	while(st.hasMoreTokens())
	{
		iNoOfKeys = iNoOfKeys + 1;
		aList.add(st.nextElement());
		if(i == 1)
		iNoOfCols = iNoOfCols + 1;

	}
	*/
	i = i + 1;
	}
	aList.set(0, "" + iNoOfCols);
	wr.close();
	rd.close();


	return aList;
}

	public ArrayList getSnapShotResults(String sQuery) throws Exception
	{
		String sParseString = sQuery;

		BufferedReader bReader = new BufferedReader(new StringReader(sParseString));
		String thisLine = "";
		String sParseString1 = "";
		int pIndex, tIndex = 0;
		while((thisLine = bReader.readLine()) != null)
		{

			pIndex = thisLine.indexOf("--");
			if(pIndex != -1){
				thisLine = thisLine.substring(0, pIndex);
			}
			sParseString1 = sParseString1 + " " + thisLine.trim();
		}
		System.out.println("sParseString1 "+sParseString1);
		sParseString = sParseString1.toLowerCase();

		pIndex = sParseString.indexOf(" top ");
		if(pIndex != -1)
		{

			tIndex = sParseString.indexOf(" ", pIndex+6);
			sParseString = sParseString.substring(0, pIndex) + " TOP 5 " + sParseString.substring(tIndex);
		}
		else
		{
			pIndex = sParseString.indexOf("select");
			sParseString = " SELECT TOP 5 " + sParseString.substring(pIndex+7);
		}


		int i = 1;
		int iNoOfCols = 0;
		ArrayList aList = new ArrayList();
		aList.add("" + iNoOfCols);

        String data = URLEncoder.encode("format", "UTF-8") + "=" + URLEncoder.encode("csv", "UTF-8");
        data += "&" + URLEncoder.encode("cmd", "UTF-8") + "=" + URLEncoder.encode(sParseString, "UTF-8");

       // System.out.println("sParseString "+sParseString);
        // Send data
        URL url = new URL("http://cas.sdss.org/dr7/en/tools/search/x_sql.asp");
        URLConnection conn = url.openConnection();
        conn.setDoOutput(true);
        OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream());
        wr.write(data);
        wr.flush();

        // Get the response
        BufferedReader rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
        String line;
        StringTokenizer st = null;
        int iIndex = 0;
        int jIndex = 0;
        String sDummy = "";

        while ((line = rd.readLine()) != null) {


        		iIndex = line.indexOf('"');
        		jIndex = line.indexOf('"', iIndex+1);

        		while(iIndex != -1)
        		{
        			sDummy = line.substring(iIndex, jIndex);
        			sDummy = sDummy.replaceAll(",", ";");
        			line = line.substring(0, iIndex) + sDummy + line.substring(jIndex);
            		iIndex = line.indexOf('"', jIndex+1);
            		jIndex = line.indexOf('"', iIndex+1);
        		}

        		iIndex = 0;
        		jIndex = line.indexOf(',');
        		while(jIndex != -1)
        		{
        			sDummy = line.substring(iIndex, jIndex);
        			if(sDummy == null)
        				sDummy = " ";
         			aList.add(sDummy);
        			iIndex = jIndex + 1;
        			jIndex = line.indexOf(',', iIndex);
        			if(i == 1)
        			iNoOfCols = iNoOfCols + 1;
        		}
        		if(i == 1)
        			iNoOfCols = iNoOfCols + 1;
        		aList.add(line.substring(iIndex));

        		/*st = new StringTokenizer(line, ",");

        		while(st.hasMoreTokens())
        		{
        			iNoOfKeys = iNoOfKeys + 1;
        			aList.add(st.nextElement());
        			if(i == 1)
        			iNoOfCols = iNoOfCols + 1;

        		}
        		*/
           		i = i + 1;
        }



        aList.set(0, "" + iNoOfCols);
        wr.close();
        rd.close();

		return aList;
	}


	public ArrayList getPrimaryKeysOfTables() throws Exception {
		ArrayList aList = new ArrayList();

			String sTxtStoreQuery = sTxtQuery;

			// Getting all the views list
			Hashtable hViews = new Hashtable<String, ArrayList>();

			ArrayList aTables = new ArrayList();

			ArrayList aViews = new ArrayList();
			ArrayList aSelectedTables = new ArrayList();
			ArrayList aKeyAppenders = new ArrayList();
			ArrayList aKeys = new ArrayList();
			ArrayList aRemovals = new ArrayList();
			ArrayList aInvalidRemovals = new ArrayList();

			String sParent = "";
			String sParseString = sTxtStoreQuery;
			String sDummy1 = "";
			String thisLine = "";
			String sParseString1 = "";
			String sDummy = "";
			String sFromString = "";
			String sSelectString = "";

			int iIndex = 0;
			int jIndex = 0;
			int pIndex = 0;

			boolean bFromFlag = false;
			boolean bASFlag = false;
			boolean bFlag = false;
			boolean bRemovalFlag = false;


			sTxtQuery = "select viewname, parent from dbviewcols";
			aList = getSkyServerResults();
			aList.remove(0);
			aList.remove(1);
			aList.remove(2);

			aLogic.add("The user query is: " + sTxtStoreQuery);

			for (int i = 0; i < aList.size(); i = i + 2) {
				if (hViews.get(aList.get(i).toString()) == null) {
					aTables = new ArrayList();
					aTables.add(aList.get(i + 1).toString());
					hViews.put(aList.get(i).toString(), aTables);
					aViews.add(aList.get(i).toString());
				} else {
					aTables = (ArrayList) hViews.get(aList.get(i).toString());
					aTables.add(aList.get(i + 1).toString());
					hViews.put(aList.get(i).toString(), aTables);
					// aViews.add(aList.get(i).toString());
				}
			}

			// Getting all the table list
			sTxtQuery = "select distinct tablename from IndexMap";

			aTables = getSkyServerResults();
			aTables.remove(0);
			aTables.remove(1);

			// Removing all the comments from the User Query
			BufferedReader bReader = new BufferedReader(new StringReader(
					sParseString));
			while ((thisLine = bReader.readLine()) != null) {

				thisLine = thisLine.toLowerCase();
				pIndex = thisLine.indexOf("--");
				if (pIndex != -1) {
					thisLine = thisLine.substring(0, pIndex);
				}
				sDummy1 = sDummy1 + thisLine;

				iIndex = thisLine.indexOf("from");
				jIndex = thisLine.indexOf("join");

				if (bFromFlag) {
					pIndex = thisLine.indexOf("(");
					if (pIndex == -1)
						sParseString1 = sParseString1 + " " + thisLine.trim();

					bFromFlag = false;
				}

				if (thisLine != null && thisLine.trim().length() > 2) {
					if (thisLine.substring(0, thisLine.length() - 1).trim()
							.equals("from")) {
						bFromFlag = true;
					}
				}

				if (iIndex != -1)
					sParseString1 = sParseString1 + " " + thisLine.trim();

				if (jIndex != -1)
					sParseString1 = sParseString1 + " " + thisLine.trim();

			}

			sParseString = sDummy1;
			sParseString1 = sParseString1.toLowerCase();
			sCQuery = sParseString;
			iIndex = 0;
			pIndex = sParseString1.lastIndexOf("where");

			if (pIndex != -1)
				sParseString1 = sParseString1.substring(iIndex, pIndex);

			sTxtQuery = sTxtStoreQuery;
			sTxtQuery = sParseString1.toLowerCase();

			StringTokenizer st = new StringTokenizer(sTxtQuery, " ");
			while (st.hasMoreTokens()) {
				sDummy = (String) st.nextElement();

				if (bASFlag) {
					bASFlag = false;

					if (sDummy.equals("as")) {
						bASFlag = true;
						continue;
					} else
						aKeyAppenders.add(sDummy);

				} else if (sDummy.equals("as")) {
					bASFlag = true;
				} else {

					for (int i = 0; i < aViews.size(); i++) {
						if (sDummy.equals(aViews.get(i).toString()
								.toLowerCase())) {
							//System.out.println(sDummy);

							sParent = aViews.get(i).toString();
							while (sParent != null) {
								//System.out.println(i + " i " + sParent);
								sDummy = sParent;
								aList = (ArrayList) hViews.get(sParent.trim());
								if (aList == null)
									sParent = null;
								else
									sParent = aList.get(0).toString();

							}
							aSelectedTables.add(sDummy);
							bASFlag = true;
						}
					}

					for (int i = 0; i < aTables.size(); i++) {
						if (sDummy.equals(aTables.get(i).toString()
								.toLowerCase())) {
							aSelectedTables.add(aTables.get(i).toString());
							bASFlag = true;
						}
					}

				}

			}

			sParent = "";
			sDummy = "";
			// Get the Keys

			for (int i = 0; i < aSelectedTables.size(); i++) {

				sTxtQuery = "select fieldList from IndexMap where type = 'primary key' and tableName= '"
						+ aSelectedTables.get(i).toString() + "'";
				aList = getSkyServerResults();
				if (aList != null && aList.size() > 2)
					sParent = aList.get(2).toString();

				sParent = sParent.replace('"', ' ');
				st = new StringTokenizer(sParent, ";");
				while (st.hasMoreTokens()) {
					sDummy = "";
					if (aKeyAppenders != null && aKeyAppenders.size() > 0 && i < aKeyAppenders.size())
						sDummy = aKeyAppenders.get(i).toString() + ".";

					sDummy = sDummy + st.nextToken().toString().trim();

					sDummy = sDummy.replaceAll(",", "").trim();
					aKeys.add(sDummy);
					aKeys.add(aSelectedTables.get(i));
				}

			}

			//System.out.println(aKeys);

			// Get the Key Results

			sParseString1 = sParseString.toLowerCase();
			iIndex = sParseString1.indexOf("from");

			if (iIndex != -1)
				sFromString = sParseString1.substring(iIndex);

			iIndex = sFromString.lastIndexOf("group");
			if (iIndex != -1)
			{
				jIndex = sFromString.indexOf(") as", iIndex+1);
				if(jIndex == -1)
					sFromString = sFromString.substring(0, iIndex);
			}


			sParseString1 = sParseString.toLowerCase();
			iIndex = sParseString1.indexOf(" top ");

			sParent = "";
			if (iIndex != -1) {
				iIndex = sParseString1.indexOf(" ", iIndex + 1);

				// System.out.println(iIndex);
				iIndex = sParseString1.indexOf(" ", iIndex + 1);
				// System.out.println(iIndex);

				sParseString1 = sParseString1.substring(0, iIndex);
				sSelectString = sParseString1;
			} else {
				sSelectString = "select " + sParent;
			}

			//System.out.println(sParent);

			sDummy = "";

			for (int j = 0; j < aKeys.size(); j = j + 2) {

				sParent = "";
				for (int i = 0; i < aKeys.size(); i = i + 2) {

					bRemovalFlag = false;
					for (int k = 0; k < aRemovals.size(); k++) {
						if (aKeys.get(i).toString().equals(
								aRemovals.get(k).toString()))
							bRemovalFlag = true;

					}

					bFlag = false;
					for (int k = 0; k < aInvalidRemovals.size(); k++) {
						iIndex = aKeys.get(i).toString().indexOf('.');
						if (iIndex != -1)
							sDummy = aKeys.get(i).toString().substring(
									iIndex + 1);
						else
							sDummy = aKeys.get(i).toString();

						if (sDummy.equals(aInvalidRemovals.get(k).toString()))
							bFlag = true;

					}

					if (!bRemovalFlag && !bFlag)
						sParent = sParent + aKeys.get(i) + " ,";

				}
				if(sParent.length() > 1)
					sParent = sParent.substring(0, sParent.length() - 1);

				sTxtQuery = sSelectString + " " + sParent + " " + sFromString;
				System.out.println(sTxtQuery);
				aList = getSkyServerResults();


				sDummy = "";
				if (aList != null
						&& aList.size() > 3 && aList.get(3).toString()
								.indexOf("could not be bound") != -1) {
					sDummy = aList.get(3).toString();
					iIndex = sDummy.indexOf('"');
					sDummy = sDummy.substring(iIndex + 1);
					iIndex = sDummy.indexOf('"');
					sDummy = sDummy.substring(0, iIndex);

					aRemovals.add(sDummy);
					//System.out.println(sDummy);
				} else if (aList != null
						&& aList.size() > 3 && aList.get(3).toString().indexOf(
								"Invalid column name") != -1) {
					sDummy = aList.get(3).toString();
					iIndex = sDummy.indexOf("'");
					sDummy = sDummy.substring(iIndex + 1);
					iIndex = sDummy.indexOf("'");
					sDummy = sDummy.substring(0, iIndex);

					aInvalidRemovals.add(sDummy);
					//System.out.println(sDummy);
					//System.out.println(aList);
				} else {

					break;
				}

			}

			int t = 0;
			//System.out.println(aKeys);
			for (int i = 1; i <= Integer.parseInt(aList.get(0).toString()); i++) {
				aList.set(i, aKeys.get(t+1));
				t= t + 2;
			}

			aLogic.add("Altered query to get the Tuple ID's "+ sTxtQuery);
			aLogic.add("Number of tuple's touched by User Query is " + aList.size());

			return aList;

	}



	public String getSTxtQueryStore() {
		return sTxtQueryStore;
	}

	public void setSTxtQueryStore(String txtQueryStore) {
		sTxtQueryStore = txtQueryStore;
	}

	public ArrayList getAKeys() {
		return aKeys;
	}

	public void setAKeys(ArrayList keys) {
		aKeys = keys;
	}

	public ArrayList getAKeyResults() {
		return aKeyResults;
	}

	public void setAKeyResults(ArrayList keyResults) {
		aKeyResults = keyResults;
	}

	public ArrayList getALogic() {
		return aLogic;
	}

	public void setALogic(ArrayList logic) {
		aLogic = logic;
	}

	public String getSCQuery() {
		return sCQuery;
	}

	public void setSCQuery(String query) {
		sCQuery = query;
	}



}
